OML4SQL Classification DT.dsnb

[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Classification DT","description":null,"readOnly":false,"type":"medium","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1714746817267,"interpreter":"md.medium","endTime":1714746817417,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Classification Modeling to Predict Target Customers using Decision Tree","","In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification decision tree models using the SH schema data. All processing occurs inside Oracle Autonomous Database.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1714746817491,"interpreter":"md.medium","endTime":1714746817584,"results":[{"message":"<h2 id=\"classification-modeling-to-predict-target-customers-using-decision-tree\">Classification Modeling to Predict Target Customers using Decision Tree<\/h2>\n<p>In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification decision tree models using the SH schema data. All processing occurs inside Oracle Autonomous Database.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":9,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","<dl>","<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>","<\/dl>"],"enabled":true,"result":{"startTime":1714746817661,"interpreter":"md.medium","endTime":1714746817723,"results":[{"message":"<dl>\n<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>\n<\/dl>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":3,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADW Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a>","* <a href=\"https://oracle.com/goto/ml-decision-tree\" target=\"_blank\">OML Decision Tree<\/a>"],"enabled":true,"result":{"startTime":1714746817794,"interpreter":"md.medium","endTime":1714746817869,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADW Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a><\/li>\n<li><a href=\"https://oracle.com/goto/ml-decision-tree\" target=\"_blank\">OML Decision Tree<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the SUPPLEMENTARY_DEMOGRAPHICS data ","message":["%sql","","SELECT * ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714746817942,"interpreter":"sql.medium","endTime":1714746820943,"results":[{"message":"CUST_ID\tEDUCATION\tOCCUPATION\tHOUSEHOLD_SIZE\tYRS_RESIDENCE\tAFFINITY_CARD\tBULK_PACK_DISKETTES\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tBOOKKEEPING_APPLICATION\tPRINTER_SUPPLIES\tY_BOX_GAMES\tOS_DOC_SET_KANJI\tCOMMENTS\n102547\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n101050\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n100040\t11th\tSales\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n102117\tHS-grad\tFarming\t1\t0\t0\t0\t0\t0\t1\t1\t1\t0\t\n101074\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n104179\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n100417\t11th\tHandler\t1\t1\t0\t0\t0\t0\t0\t1\t1\t0\t\n101146\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n103420\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101987\t< Bach.\tOther\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101279\t< Bach.\tOther\t1\t1\t0\t0\t0\t0\t1\t1\t1\t0\t\n102106\t< Bach.\tCleric.\t1\t1\t0\t0\t0\t0\t1\t1\t1\t0\t\n102591\tHS-grad\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101869\tHS-grad\tOther\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n103169\tHS-grad\tSales\t1\t1\t0\t0\t0\t0\t1\t1\t1\t0\t\n103552\tHS-grad\tHandler\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101616\tBach.\tTechSup\t1\t2\t0\t0\t0\t0\t1\t1\t1\t0\t\n104096\t< Bach.\tOther\t1\t2\t0\t1\t1\t0\t1\t1\t1\t0\t\n102171\t< Bach.\tCleric.\t1\t2\t0\t0\t0\t0\t1\t1\t1\t0\t\n100846\t< Bach.\tHandler\t1\t2\t0\t1\t1\t0\t1\t1\t1\t0\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in SUPPLEMENTARY_DEMOGRAPHICS table","message":["%sql","","SELECT COUNT(*) FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"],"enabled":true,"result":{"startTime":1714746821017,"interpreter":"sql.medium","endTime":1714746821239,"results":[{"message":"COUNT(*)\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":4,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"showSeries\":[\"COUNT\"],\"aggregationOption\":\"Last\",\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"AFFINITY_CARD\",\"COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show distribution of AFFINITY_CARD responders","message":["%sql","","SELECT AFFINITY_CARD, count(*) COUNT","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","GROUP BY AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1714746821310,"interpreter":"sql.medium","endTime":1714746821430,"results":[{"message":"AFFINITY_CARD\tCOUNT\n0\t3428\n1\t1072\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"groupByColumns\":[\"AFFINITY_CARD\",\"HOUSEHOLD_SIZE\"],\"aggregationOption\":\"Last\",\"series\":{\"availableSeriesElements\":[{\"id\":\"NUM_CUSTOMERS\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1},\"table\":{\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1},\"area\":{\"version\":1},\"raw\":{}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD ","message":["%sql","","SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS ","GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD","ORDER BY HOUSEHOLD_SIZE;"],"enabled":true,"result":{"startTime":1714746854906,"interpreter":"sql.medium","endTime":1714746855002,"results":[{"message":"NUM_CUSTOMERS\tHOUSEHOLD_SIZE\tAFFINITY_CARD\n681\t1\t0\n11\t1\t1\n1040\t2\t0\n109\t2\t1\n973\t3\t0\n814\t3\t1\n112\t4-5\t0\n107\t4-5\t1\n146\t6-8\t0\n2\t6-8\t1\n476\t9+\t0\n29\t9+\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"showSeries\":[\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"aggregationOption\":\"Last\",\"visualizations\":{\"stackValue\":\"on\"},\"series\":{\"availableSeriesElements\":[{\"id\":\"AFFINITY_1_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0},{\"id\":\"AFFINITY_0_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(50, 146, 94)\",\"borderWidth\":0,\"color\":\"rgb(50, 146, 94)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(50, 146, 94)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"HOUSEHOLD_SIZE\",\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"version\":1},\"table\":{\"columnsToShow\":[\"NUM_CUSTOMERS\"],\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1},\"area\":{\"version\":1},\"raw\":{}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD ","message":["%sql","","SELECT HOUSEHOLD_SIZE,"," SUM(CASE WHEN AFFINITY_CARD = 1 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_1_COUNT,"," SUM(CASE WHEN AFFINITY_CARD = 0 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_0_COUNT","FROM (SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD "," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS "," GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD)","GROUP BY HOUSEHOLD_SIZE","ORDER BY HOUSEHOLD_SIZE"],"enabled":true,"result":{"startTime":1714747761776,"interpreter":"sql.medium","endTime":1714747761895,"results":[{"message":"HOUSEHOLD_SIZE\tAFFINITY_1_COUNT\tAFFINITY_0_COUNT\n1\t11\t681\n2\t109\t1040\n3\t814\t973\n4-5\t107\t112\n6-8\t2\t146\n9+\t29\t476\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md ","","## Create and display a view DEMOGRAPHICS4_V with desired columns for analysis"],"enabled":true,"result":{"startTime":1714746821693,"interpreter":"md.medium","endTime":1714746821756,"results":[{"message":"<h2 id=\"create-and-display-a-view-demographics4_v-with-desired-columns-for-analysis\">Create and display a view DEMOGRAPHICS4_V with desired columns for analysis<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":null,"message":["%script","","CREATE OR REPLACE VIEW DEMOGRAPHICS4_V "," AS SELECT AFFINITY_CARD, CUST_ID, FLAT_PANEL_MONITOR, BOOKKEEPING_APPLICATION,"," BULK_PACK_DISKETTES, EDUCATION, HOME_THEATER_PACKAGE, "," HOUSEHOLD_SIZE, OCCUPATION, OS_DOC_SET_KANJI,"," PRINTER_SUPPLIES, YRS_RESIDENCE, Y_BOX_GAMES"," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"," "],"enabled":true,"result":{"startTime":1714746821831,"interpreter":"script.medium","endTime":1714746822149,"results":[{"message":"\nView DEMOGRAPHICS4_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":4,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":null,"message":["%sql ","","SELECT * ","FROM DEMOGRAPHICS4_V","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714746822220,"interpreter":"sql.medium","endTime":1714746822337,"results":[{"message":"AFFINITY_CARD\tCUST_ID\tFLAT_PANEL_MONITOR\tBOOKKEEPING_APPLICATION\tBULK_PACK_DISKETTES\tEDUCATION\tHOME_THEATER_PACKAGE\tHOUSEHOLD_SIZE\tOCCUPATION\tOS_DOC_SET_KANJI\tPRINTER_SUPPLIES\tYRS_RESIDENCE\tY_BOX_GAMES\n0\t102547\t1\t0\t1\t10th\t0\t1\tOther\t0\t1\t0\t1\n0\t101050\t1\t0\t1\t10th\t0\t1\tOther\t0\t1\t0\t1\n0\t100040\t1\t0\t1\t11th\t0\t1\tSales\t0\t1\t0\t1\n0\t102117\t0\t1\t0\tHS-grad\t0\t1\tFarming\t0\t1\t0\t1\n0\t101074\t1\t0\t1\t10th\t0\t1\tHandler\t0\t1\t1\t1\n0\t104179\t1\t0\t1\t10th\t0\t1\tHandler\t0\t1\t1\t1\n0\t100417\t0\t0\t0\t11th\t0\t1\tHandler\t0\t1\t1\t1\n0\t101146\t1\t1\t1\t< Bach.\t0\t1\t?\t0\t1\t1\t1\n0\t103420\t1\t1\t1\t< Bach.\t0\t1\t?\t0\t1\t1\t1\n0\t101987\t1\t1\t1\t< Bach.\t0\t1\tOther\t0\t1\t1\t1\n0\t101279\t0\t1\t0\t< Bach.\t0\t1\tOther\t0\t1\t1\t1\n0\t102106\t0\t1\t0\t< Bach.\t0\t1\tCleric.\t0\t1\t1\t1\n0\t102591\t1\t1\t1\tHS-grad\t0\t1\t?\t0\t1\t1\t1\n0\t101869\t1\t1\t1\tHS-grad\t0\t1\tOther\t0\t1\t1\t1\n0\t103169\t0\t1\t0\tHS-grad\t0\t1\tSales\t0\t1\t1\t1\n0\t103552\t1\t1\t1\tHS-grad\t0\t1\tHandler\t0\t1\t1\t1\n0\t101616\t0\t1\t0\tBach.\t0\t1\tTechSup\t0\t1\t2\t1\n0\t104096\t1\t1\t1\t< Bach.\t0\t1\tOther\t0\t1\t2\t1\n0\t102171\t0\t1\t0\t< Bach.\t0\t1\tCleric.\t0\t1\t2\t1\n0\t100846\t1\t1\t1\t< Bach.\t0\t1\tHandler\t0\t1\t2\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create train and test data sets (60/40) for model build and test","message":["%script","","CREATE OR REPLACE VIEW TRAIN_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V SAMPLE (60) SEED (1);","CREATE OR REPLACE VIEW TEST_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V MINUS SELECT * FROM TRAIN_DATA_CLAS;"],"enabled":true,"result":{"startTime":1714746822414,"interpreter":"script.medium","endTime":1714746822506,"results":[{"message":"\nView TRAIN_DATA_CLAS created.\n\n\n---------------------------\n\nView TEST_DATA_CLAS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for DT ","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","A complete list of settings can be found in the Documentation link:","","- Algorithm Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566');\">Decision Tree<\/a>","","- Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a>","","- Specify a row weight column ","> v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>';","- Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';","- Specify Tree impurity metric for Decision Tree. Tree algorithms seek the best test question for splitting data at each node. The best splitter and split values are those that result in the largest increase in target value homogeneity (purity) for the entities in the node. Purity is by a metric. Decision trees can use either Gini `TREE_IMPURITY_GINI` or entropy `TREE_IMPURITY_ENTROPY` as the purity metric. By default, the algorithm uses `TREE_IMPURITY_GINI`.","> v_setlst('TREE_IMPURITY_METRIC') := 'TREE_IMPURITY_GINI';","- Specify the criteria for splits regarding the maximum tree depth (the maximum number of nodes between the root and any leaf node, including the leaf node). For Decision Tree, it requires a number between 2 and 20, and the default is 7. For Random Forest it is a number between 2 and 100, and the default is 16.","> v_setlst('TREE_TERM_MAX_DEPTH') := '7';","- Specify the minimum number of training rows in a node expressed as a percentage of the rows in the training data. It requires a number between 0 and 10. The default is 0.05, indicating 0.05%. ","> v_setlst('TREE_TERM_MINPCT_NODE') := '0.05';","- Specify the minimum number of rows required to consider splitting a node expressed as a percentage of the training rows. It requires a number greater than 0, and smaller or equal to 20. The default is 0.1, indicating 0.1%. ","> v_setlst('TREE_TERM_MINPCT_SPLIT') := '0.1';","- Specify The minimum number of rows in a node. It requires a number greater than or equal to zero. The default is 10. ","> v_setlst('TREE_TERM_MINREC_NODE') := '10';","- Specify the criteria for splits regarding the minimum number of records in a parent node expressed as a value. No split is attempted if the number of records is below this value. It requires a number greater than 1. The default is 20. ","> v_setlst('TREE_TERM_MINREC_SPLIT') := '20';","- Specify the maximum number of bins for each attribute. For Decision Tree it requires a number between 2 and 2,147,483,647, with the default value of 32. For Random Forest it requires a number between 2 and 254, with the default value of 32.","> v_setlst('CLAS_MAX_SUP_BINS') := '32'; "],"enabled":true,"result":{"startTime":1714746822590,"interpreter":"md.medium","endTime":1714746822668,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-dt\">Examples of possible setting overrides for DT<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>\n<p>Algorithm Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566');\">Decision Tree<\/a><\/p>\n<\/li>\n<li>\n<p>Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a><\/p>\n<\/li>\n<li>\n<p>Specify a row weight column<\/p>\n<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '&lt;row_weight_column_name&gt;';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify Tree impurity metric for Decision Tree. Tree algorithms seek the best test question for splitting data at each node. The best splitter and split values are those that result in the largest increase in target value homogeneity (purity) for the entities in the node. Purity is by a metric. Decision trees can use either Gini <code>TREE_IMPURITY_GINI<\/code> or entropy <code>TREE_IMPURITY_ENTROPY<\/code> as the purity metric. By default, the algorithm uses <code>TREE_IMPURITY_GINI<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_IMPURITY_METRIC') := 'TREE_IMPURITY_GINI';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the criteria for splits regarding the maximum tree depth (the maximum number of nodes between the root and any leaf node, including the leaf node). For Decision Tree, it requires a number between 2 and 20, and the default is 7. For Random Forest it is a number between 2 and 100, and the default is 16.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MAX_DEPTH') := '7';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the minimum number of training rows in a node expressed as a percentage of the rows in the training data. It requires a number between 0 and 10. The default is 0.05, indicating 0.05%.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINPCT_NODE') := '0.05';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the minimum number of rows required to consider splitting a node expressed as a percentage of the training rows. It requires a number greater than 0, and smaller or equal to 20. The default is 0.1, indicating 0.1%.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINPCT_SPLIT') := '0.1';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify The minimum number of rows in a node. It requires a number greater than or equal to zero. The default is 10.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINREC_NODE') := '10';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the criteria for splits regarding the minimum number of records in a parent node expressed as a value. No split is attempted if the number of records is below this value. It requires a number greater than 1. The default is 20.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINREC_SPLIT') := '20';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of bins for each attribute. For Decision Tree it requires a number between 2 and 2,147,483,647, with the default value of 32. For Random Forest it requires a number between 2 and 254, with the default value of 32.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('CLAS_MAX_SUP_BINS') := '32';<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Decision Tree model using default settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('DT_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_DECISION_TREE';"," "," DBMS_DATA_MINING.CREATE_MODEL2("," 'DT_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1714746822754,"interpreter":"script.medium","endTime":1714746825452,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Decision Tree Model using full settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('DT_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_DECISION_TREE';"," "," v_setlst('TREE_IMPURITY_METRIC') := 'TREE_IMPURITY_GINI';"," v_setlst('TREE_TERM_MAX_DEPTH') := '7';"," v_setlst('TREE_TERM_MINPCT_NODE') := '0.05'; "," v_setlst('TREE_TERM_MINPCT_SPLIT') := '0.1';"," v_setlst('TREE_TERM_MINREC_NODE') := '10';"," v_setlst('TREE_TERM_MINREC_SPLIT') := '20';"," v_setlst('CLAS_MAX_SUP_BINS') := '32';"," "," DBMS_DATA_MINING.CREATE_MODEL2("," 'DT_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1714746825528,"interpreter":"script.medium","endTime":1714746827125,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get all views for model information","message":["%sql ","","SELECT VIEW_NAME, VIEW_TYPE "," FROM USER_MINING_MODEL_VIEWS"," WHERE MODEL_NAME='DT_CLASS_MODEL'"," ORDER BY VIEW_NAME;"," "],"enabled":true,"result":{"startTime":1714746827202,"interpreter":"sql.medium","endTime":1714746827299,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VCDT_CLASS_MODEL\tScoring Cost Matrix\nDM$VGDT_CLASS_MODEL\tGlobal Name-Value Pairs\nDM$VIDT_CLASS_MODEL\tDecision Tree Statistics\nDM$VMDT_CLASS_MODEL\tDecision Tree Build Cost Matrix\nDM$VODT_CLASS_MODEL\tDecision Tree Nodes\nDM$VPDT_CLASS_MODEL\tDecision Tree Hierarchy\nDM$VSDT_CLASS_MODEL\tComputed Settings\nDM$VTDT_CLASS_MODEL\tClassification Targets\nDM$VWDT_CLASS_MODEL\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get decision tree statistics","message":["%sql","","SELECT * from DM$VIDT_CLASS_MODEL;"],"enabled":true,"result":{"startTime":1714746827374,"interpreter":"sql.medium","endTime":1714746827693,"results":[{"message":"PARTITION_NAME\tNODE\tNODE_SUPPORT\tPREDICTED_TARGET_VALUE\tTARGET_VALUE\tTARGET_SUPPORT\n\t7\t1392\t0\t0\t1320\n\t7\t1392\t0\t1\t72\n\t12\t214\t0\t0\t115\n\t12\t214\t0\t1\t99\n\t1\t1211\t0\t0\t652\n\t1\t1211\t0\t1\t559\n\t15\t843\t0\t0\t833\n\t15\t843\t0\t1\t10\n\t16\t549\t0\t0\t487\n\t16\t549\t0\t1\t62\n\t8\t373\t1\t0\t95\n\t8\t373\t1\t1\t278\n\t10\t132\t1\t0\t59\n\t10\t132\t1\t1\t73\n\t5\t473\t0\t0\t306\n\t5\t473\t0\t1\t167\n\t14\t76\t0\t0\t50\n\t14\t76\t0\t1\t26\n\t9\t34\t0\t0\t25\n\t9\t34\t0\t1\t9\n\t0\t2679\t0\t0\t2022\n\t0\t2679\t0\t1\t657\n\t13\t259\t0\t0\t191\n\t13\t259\t0\t1\t68\n\t4\t672\t0\t0\t473\n\t4\t672\t0\t1\t199\n\t11\t199\t0\t0\t167\n\t11\t199\t0\t1\t32\n\t6\t1468\t0\t0\t1370\n\t6\t1468\t0\t1\t98\n\t2\t407\t1\t0\t120\n\t2\t407\t1\t1\t287\n\t3\t804\t0\t0\t532\n\t3\t804\t0\t1\t272\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get node information","message":["%sql","","SELECT * from DM$VODT_CLASS_MODEL;"],"enabled":true,"result":{"startTime":1714746827785,"interpreter":"sql.medium","endTime":1714746828598,"results":[{"message":"PARTITION_NAME\tNODE\tNODE_SUPPORT\tPREDICTED_TARGET_VALUE\tPARENT\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tOPERATOR\tVALUE\n\t8\t373\t1\t2\tY_BOX_GAMES\t\t<=\t<Element>5.0E-001<\/Element>\n\t3\t804\t0\t1\tOCCUPATION\t\tIN\t<Element>Other<\/Element><Element>Sales<\/Element><Element>Handler<\/Element><Element>Machine<\/Element><Element>House-s<\/Element><Element>?<\/Element><Element>TechSup<\/Element><Element>Cleric.<\/Element><Element>Transp.<\/Element><Element>Armed-F<\/Element><Element>Farming<\/Element><Element>Crafts<\/Element>\n\t4\t672\t0\t3\tEDUCATION\t\tIN\t<Element>1st-4th<\/Element><Element>Profsc<\/Element><Element>10th<\/Element><Element>PhD<\/Element><Element>Masters<\/Element><Element>HS-grad<\/Element><Element>12th<\/Element><Element>11th<\/Element><Element>7th-8th<\/Element><Element>Presch.<\/Element><Element>Assoc-V<\/Element><Element>&lt; Bach.<\/Element><Element>9th<\/Element><Element>5th-6th<\/Element>\n\t5\t473\t0\t4\tYRS_RESIDENCE\t\t>\t<Element>3.5E+000<\/Element>\n\t9\t34\t0\t2\tY_BOX_GAMES\t\t>\t<Element>5.0E-001<\/Element>\n\t1\t1211\t0\t0\tHOUSEHOLD_SIZE\t\tIN\t<Element>4-5<\/Element><Element>3<\/Element>\n\t11\t199\t0\t4\tYRS_RESIDENCE\t\t<=\t<Element>3.5E+000<\/Element>\n\t10\t132\t1\t3\tEDUCATION\t\tIN\t<Element>Assoc-A<\/Element><Element>Bach.<\/Element>\n\t13\t259\t0\t5\tFLAT_PANEL_MONITOR\t\t>\t<Element>5.0E-001<\/Element>\n\t6\t1468\t0\t0\tHOUSEHOLD_SIZE\t\tIN\t<Element>9+<\/Element><Element>6-8<\/Element><Element>1<\/Element><Element>2<\/Element>\n\t14\t76\t0\t6\tEDUCATION\t\tIN\t<Element>PhD<\/Element><Element>Masters<\/Element><Element>Profsc<\/Element>\n\t2\t407\t1\t1\tOCCUPATION\t\tIN\t<Element>Prof.<\/Element><Element>Protec.<\/Element><Element>Exec.<\/Element>\n\t0\t2679\t0\t\t\t\t\t\n\t16\t549\t0\t7\tYRS_RESIDENCE\t\t>\t<Element>3.5E+000<\/Element>\n\t12\t214\t0\t5\tFLAT_PANEL_MONITOR\t\t<=\t<Element>5.0E-001<\/Element>\n\t7\t1392\t0\t6\tEDUCATION\t\tIN\t<Element>Presch.<\/Element><Element>Bach.<\/Element><Element>&lt; Bach.<\/Element><Element>7th-8th<\/Element><Element>5th-6th<\/Element><Element>HS-grad<\/Element><Element>Assoc-A<\/Element><Element>12th<\/Element><Element>1st-4th<\/Element><Element>11th<\/Element><Element>10th<\/Element><Element>Assoc-V<\/Element><Element>9th<\/Element>\n\t15\t843\t0\t7\tYRS_RESIDENCE\t\t<=\t<Element>3.5E+000<\/Element>\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get tree hierarchy information","message":["%sql","","SELECT * from DM$VPDT_CLASS_MODEL;"],"enabled":true,"result":{"startTime":1714746828686,"interpreter":"sql.medium","endTime":1714746828872,"results":[{"message":"PARTITION_NAME\tPARENT\tSPLIT_TYPE\tNODE\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tOPERATOR\tVALUE\n\t0\tSurrogate\t6\tYRS_RESIDENCE\t\t<=\t<Element>3.5E+000<\/Element>\n\t2\tSurrogate\t8\tYRS_RESIDENCE\t\t>\t<Element>2.5E+000<\/Element>\n\t4\tMain\t5\tYRS_RESIDENCE\t\t>\t<Element>3.5E+000<\/Element>\n\t4\tMain\t11\tYRS_RESIDENCE\t\t<=\t<Element>3.5E+000<\/Element>\n\t1\tSurrogate\t2\tEDUCATION\t\tIN\t<Element>Assoc-A<\/Element><Element>Bach.<\/Element><Element>Profsc<\/Element><Element>PhD<\/Element><Element>Masters<\/Element>\n\t1\tSurrogate\t3\tEDUCATION\t\tIN\t<Element>10th<\/Element><Element>Presch.<\/Element><Element>5th-6th<\/Element><Element>Assoc-V<\/Element><Element>1st-4th<\/Element><Element>&lt; Bach.<\/Element><Element>7th-8th<\/Element><Element>12th<\/Element><Element>HS-grad<\/Element><Element>9th<\/Element><Element>11th<\/Element>\n\t2\tMain\t9\tY_BOX_GAMES\t\t>\t<Element>5.0E-001<\/Element>\n\t2\tSurrogate\t9\tYRS_RESIDENCE\t\t<=\t<Element>2.5E+000<\/Element>\n\t3\tMain\t10\tEDUCATION\t\tIN\t<Element>Assoc-A<\/Element><Element>Bach.<\/Element>\n\t6\tMain\t14\tEDUCATION\t\tIN\t<Element>PhD<\/Element><Element>Profsc<\/Element><Element>Masters<\/Element>\n\t7\tMain\t16\tYRS_RESIDENCE\t\t>\t<Element>3.5E+000<\/Element>\n\t0\tSurrogate\t1\tYRS_RESIDENCE\t\t>\t<Element>3.5E+000<\/Element>\n\t2\tMain\t8\tY_BOX_GAMES\t\t<=\t<Element>5.0E-001<\/Element>\n\t4\tSurrogate\t5\tHOME_THEATER_PACKAGE\t\t>\t<Element>5.0E-001<\/Element>\n\t4\tSurrogate\t11\tHOME_THEATER_PACKAGE\t\t<=\t<Element>5.0E-001<\/Element>\n\t5\tSurrogate\t12\tBULK_PACK_DISKETTES\t\t<=\t<Element>5.0E-001<\/Element>\n\t1\tMain\t3\tOCCUPATION\t\tIN\t<Element>Other<\/Element><Element>Machine<\/Element><Element>House-s<\/Element><Element>?<\/Element><Element>Transp.<\/Element><Element>Armed-F<\/Element><Element>TechSup<\/Element><Element>Cleric.<\/Element><Element>Sales<\/Element><Element>Handler<\/Element><Element>Farming<\/Element><Element>Crafts<\/Element>\n\t5\tMain\t13\tFLAT_PANEL_MONITOR\t\t>\t<Element>5.0E-001<\/Element>\n\t3\tMain\t4\tEDUCATION\t\tIN\t<Element>1st-4th<\/Element><Element>PhD<\/Element><Element>Masters<\/Element><Element>HS-grad<\/Element><Element>12th<\/Element><Element>11th<\/Element><Element>Presch.<\/Element><Element>Assoc-V<\/Element><Element>&lt; Bach.<\/Element><Element>7th-8th<\/Element><Element>Profsc<\/Element><Element>10th<\/Element><Element>9th<\/Element><Element>5th-6th<\/Element>\n\t5\tSurrogate\t13\tBULK_PACK_DISKETTES\t\t>\t<Element>5.0E-001<\/Element>\n\t7\tSurrogate\t15\tHOME_THEATER_PACKAGE\t\t<=\t<Element>5.0E-001<\/Element>\n\t7\tSurrogate\t16\tHOME_THEATER_PACKAGE\t\t>\t<Element>5.0E-001<\/Element>\n\t0\tMain\t1\tHOUSEHOLD_SIZE\t\tIN\t<Element>4-5<\/Element><Element>3<\/Element>\n\t0\tMain\t6\tHOUSEHOLD_SIZE\t\tIN\t<Element>9+<\/Element><Element>6-8<\/Element><Element>1<\/Element><Element>2<\/Element>\n\t1\tMain\t2\tOCCUPATION\t\tIN\t<Element>Prof.<\/Element><Element>Protec.<\/Element><Element>Exec.<\/Element>\n\t5\tMain\t12\tFLAT_PANEL_MONITOR\t\t<=\t<Element>5.0E-001<\/Element>\n\t6\tMain\t7\tEDUCATION\t\tIN\t<Element>Presch.<\/Element><Element>HS-grad<\/Element><Element>Assoc-A<\/Element><Element>12th<\/Element><Element>Assoc-V<\/Element><Element>1st-4th<\/Element><Element>11th<\/Element><Element>10th<\/Element><Element>Bach.<\/Element><Element>&lt; Bach.<\/Element><Element>7th-8th<\/Element><Element>5th-6th<\/Element><Element>9th<\/Element>\n\t7\tMain\t15\tYRS_RESIDENCE\t\t<=\t<Element>3.5E+000<\/Element>\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Evaluate the model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE APPLY_RESULT PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN EXECUTE IMMEDIATE 'DROP TABLE LIFT_TABLE PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN"," DBMS_DATA_MINING.APPLY('DT_CLASS_MODEL','TEST_DATA_CLAS','CUST_ID','APPLY_RESULT');"," DBMS_DATA_MINING.COMPUTE_LIFT('APPLY_RESULT','TEST_DATA_CLAS','CUST_ID','AFFINITY_CARD',"," 'LIFT_TABLE','1','PREDICTION','PROBABILITY',100);","END;"],"enabled":true,"result":{"startTime":1714746828951,"interpreter":"script.medium","endTime":1714746833652,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"pie\":{\"version\":1},\"pyramid\":{\"version\":1},\"tree\":{},\"sunburst\":{},\"tag\":{},\"boxplot\":{},\"scatterplot\":{},\"map\":{\"version\":1},\"raw\":{},\"line\":{\"showSeries\":[\"GAIN_CUMULATIVE\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"GAIN_CUMULATIVE\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"QUANTILE_NUMBER\",\"GAIN_CUMULATIVE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"line","title":"View model's cumulative gains (lift) chart","message":["%sql","","SELECT QUANTILE_NUMBER, "," ROUND(GAIN_CUMULATIVE,3) GAIN_CUMULATIVE","FROM LIFT_TABLE;"],"enabled":true,"result":{"startTime":1714746833733,"interpreter":"sql.medium","endTime":1714746833855,"results":[{"message":"QUANTILE_NUMBER\tGAIN_CUMULATIVE\n1\t0.031\n2\t0.062\n3\t0.092\n4\t0.123\n5\t0.154\n6\t0.185\n7\t0.216\n8\t0.246\n9\t0.277\n10\t0.308\n11\t0.339\n12\t0.37\n13\t0.4\n14\t0.424\n15\t0.446\n16\t0.467\n17\t0.489\n18\t0.511\n19\t0.529\n20\t0.546\n21\t0.563\n22\t0.579\n23\t0.596\n24\t0.612\n25\t0.629\n26\t0.646\n27\t0.662\n28\t0.672\n29\t0.682\n30\t0.693\n31\t0.707\n32\t0.72\n33\t0.734\n34\t0.747\n35\t0.761\n36\t0.774\n37\t0.788\n38\t0.801\n39\t0.814\n40\t0.828\n41\t0.836\n42\t0.843\n43\t0.85\n44\t0.857\n45\t0.865\n46\t0.872\n47\t0.879\n48\t0.884\n49\t0.889\n50\t0.894\n51\t0.898\n52\t0.903\n53\t0.908\n54\t0.912\n55\t0.917\n56\t0.922\n57\t0.926\n58\t0.931\n59\t0.936\n60\t0.941\n61\t0.945\n62\t0.95\n63\t0.955\n64\t0.959\n65\t0.964\n66\t0.969\n67\t0.973\n68\t0.978\n69\t0.983\n70\t0.984\n71\t0.985\n72\t0.985\n73\t0.986\n74\t0.986\n75\t0.987\n76\t0.988\n77\t0.988\n78\t0.989\n79\t0.989\n80\t0.99\n81\t0.99\n82\t0.991\n83\t0.991\n84\t0.992\n85\t0.992\n86\t0.993\n87\t0.993\n88\t0.994\n89\t0.994\n90\t0.995\n91\t0.995\n92\t0.996\n93\t0.996\n94\t0.997\n95\t0.997\n96\t0.998\n97\t0.998\n98\t0.999\n99\t0.999\n100\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display customers with likelihood > 70% to be AFFINITY_CARD responders","message":["%sql","","SELECT CUST_ID, PREDICTION PRED, ROUND(PROBABILITY,3) PROB, ROUND(COST,2) COST ","FROM APPLY_RESULT ","WHERE PREDICTION = 1 AND PROBABILITY > 0.7","ORDER BY PROBABILITY DESC","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714746833931,"interpreter":"sql.medium","endTime":1714746834052,"results":[{"message":"CUST_ID\tPRED\tPROB\tCOST\n100371\t1\t0.745\t0.25\n100486\t1\t0.745\t0.25\n100527\t1\t0.745\t0.25\n100738\t1\t0.745\t0.25\n100815\t1\t0.745\t0.25\n100972\t1\t0.745\t0.25\n101444\t1\t0.745\t0.25\n101485\t1\t0.745\t0.25\n101730\t1\t0.745\t0.25\n101745\t1\t0.745\t0.25\n101803\t1\t0.745\t0.25\n101863\t1\t0.745\t0.25\n101872\t1\t0.745\t0.25\n101897\t1\t0.745\t0.25\n101968\t1\t0.745\t0.25\n102015\t1\t0.745\t0.25\n102080\t1\t0.745\t0.25\n102083\t1\t0.745\t0.25\n102104\t1\t0.745\t0.25\n102394\t1\t0.745\t0.25\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Select and view top 20 likely and unlikely AFFINITY_CARD responders ","message":["%sql ","","SELECT CUST_ID, PREDICTION, ROUND(PROBABILITY,2) PROB, ROUND(COST,2) COST","FROM APPLY_RESULT ","WHERE PREDICTION = ${PREDICTION='1','1'|'0'} ","AND ROWNUM < 21","ORDER BY PROBABILITY DESC;"," "],"enabled":true,"result":{"startTime":1714746834126,"interpreter":"sql.medium","endTime":1714746834225,"results":[{"message":"CUST_ID\tPREDICTION\tPROB\tCOST\n103000\t1\t0.01\t0.99\n101636\t1\t0.01\t0.99\n104100\t1\t0.01\t0.99\n100685\t1\t0.01\t0.99\n100732\t1\t0.01\t0.99\n100801\t1\t0.01\t0.99\n100847\t1\t0.01\t0.99\n101062\t1\t0.01\t0.99\n101153\t1\t0.01\t0.99\n101241\t1\t0.01\t0.99\n101285\t1\t0.01\t0.99\n101331\t1\t0.01\t0.99\n101358\t1\t0.01\t0.99\n101463\t1\t0.01\t0.99\n101465\t1\t0.01\t0.99\n101484\t1\t0.01\t0.99\n101556\t1\t0.01\t0.99\n101588\t1\t0.01\t0.99\n101631\t1\t0.01\t0.99\n103200\t1\t0.01\t0.99\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":"{\"PREDICTION\":\"'1'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"PREDICTION\",\"displayName\":null,\"defaultValue\":\"'1'\",\"argument\":null,\"options\":[{\"value\":\"'1'\",\"displayName\":null},{\"value\":\"'0'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Use SQL query to dynamically score and select customers with >=50% probability of being AFFINITY_CARD responders","message":["%sql","","SELECT * ","FROM (SELECT CUST_ID, "," ROUND(PREDICTION_PROBABILITY(DT_CLASS_MODEL, '1' USING A.*),3) PROBABILITY"," FROM TEST_DATA_CLAS A)","WHERE PROBABILITY > 0.7","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714746834302,"interpreter":"sql.medium","endTime":1714746834424,"results":[{"message":"CUST_ID\tPROBABILITY\n100798\t0.745\n101148\t0.745\n101516\t0.745\n101733\t0.745\n102518\t0.745\n102686\t0.745\n102713\t0.745\n103389\t0.745\n103457\t0.745\n103529\t0.745\n100035\t0.745\n100224\t0.745\n100397\t0.745\n100442\t0.745\n100701\t0.745\n100889\t0.745\n101010\t0.745\n101026\t0.745\n101044\t0.745\n101449\t0.745\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Apply the model to a single new record","message":["%sql","","-- Predict the probability of being a high AFFINITY_CARD responder (1) ","-- by providing specific attribute values for a customer.","","SELECT ROUND(PREDICTION_PROBABILITY(DT_CLASS_MODEL, '1' USING "," '3' AS HOUSEHOLD_SIZE, "," 5 AS YRS_RESIDENCE, "," 1 AS Y_BOX_GAMES),3) PROBABILITY_AFFINITY_CARD_RESPONDER","FROM DUAL;"," "],"enabled":true,"result":{"startTime":1714746834500,"interpreter":"sql.medium","endTime":1714746834586,"results":[{"message":"PROBABILITY_AFFINITY_CARD_RESPONDER\n0.462\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get prediction details","message":["%sql","","SELECT CUST_ID,"," round(PREDICTION_YRS_RES,3) PRED_YRS_RES,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute3\",17,100)),'rank=\"3\"/>') THIRD_ATTRIBUTE","FROM (SELECT CUST_ID,"," PREDICTION(DT_CLASS_MODEL USING *) PREDICTION_YRS_RES,"," PREDICTION_DETAILS(DT_CLASS_MODEL USING *) PD"," FROM TEST_DATA_CLAS"," WHERE CUST_ID < 100025"," ORDER BY CUST_ID) OUT,"," XMLTABLE('/Details'"," PASSING OUT.PD"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]',"," \"Attribute3\" XMLType PATH 'Attribute[3]') OUTPRED","FETCH FIRST 10 ROWS ONLY"," "],"enabled":true,"result":{"startTime":1714746834660,"interpreter":"sql.medium","endTime":1714746834808,"results":[{"message":"CUST_ID\tPRED_YRS_RES\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\tTHIRD_ATTRIBUTE\n100001\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".178\" \t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\".04\" \t\"EDUCATION\" actualValue=\"&lt; Bach.\" weight=\".015\" \n100004\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".178\" \t\"EDUCATION\" actualValue=\"&lt; Bach.\" weight=\".015\" \t\"YRS_RESIDENCE\" actualValue=\"5\" weight=\"-.061\" \n100006\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"9+\" weight=\".178\" \t\"YRS_RESIDENCE\" actualValue=\"2\" weight=\".04\" \t\"EDUCATION\" actualValue=\"&lt; Bach.\" weight=\".015\" \n100010\t0\t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\".135\" \t\"OCCUPATION\" actualValue=\"Crafts\" weight=\".123\" \t\"EDUCATION\" actualValue=\"HS-grad\" weight=\".042\" \n100012\t1\t\"OCCUPATION\" actualValue=\"Prof.\" weight=\".244\" \t\"HOUSEHOLD_SIZE\" actualValue=\"3\" weight=\".216\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".04\" \n100014\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".178\" \t\"EDUCATION\" actualValue=\"HS-grad\" weight=\".015\" \t\"YRS_RESIDENCE\" actualValue=\"5\" weight=\"-.061\" \n100017\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"9+\" weight=\".178\" \t\"EDUCATION\" actualValue=\"&lt; Bach.\" weight=\".015\" \t\"YRS_RESIDENCE\" actualValue=\"5\" weight=\"-.061\" \n100023\t0\t\"OCCUPATION\" actualValue=\"Sales\" weight=\".123\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".091\" \t\"EDUCATION\" actualValue=\"HS-grad\" weight=\".042\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Create Classification Model using Settings Table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model."],"enabled":true,"result":{"startTime":1714746834885,"interpreter":"md.medium","endTime":1714746834945,"results":[{"message":"<h2 id=\"create-classification-model-using-settings-table\">Create Classification Model using Settings Table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build classification model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE N1_BUILD_SETTINGS PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","CREATE TABLE N1_BUILD_SETTINGS (setting_name VARCHAR2(30),setting_value VARCHAR2(4000));","/","BEGIN DBMS_DATA_MINING.DROP_MODEL('CLASS_MODEL_2');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","BEGIN"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_DECISION_TREE');"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('PREP_AUTO', 'ON');",""," DBMS_DATA_MINING.CREATE_MODEL('CLASS_MODEL_2', 'CLASSIFICATION', 'TRAIN_DATA_CLAS', 'CUST_ID',' AFFINITY_CARD', 'N1_BUILD_SETTINGS');"," DBMS_OUTPUT.PUT_LINE ('Created model: CLASS_MODEL_2 ');","END;"],"enabled":true,"result":{"startTime":1714746835019,"interpreter":"script.medium","endTime":1714746836914,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable N1_BUILD_SETTINGS created.\n\n\n---------------------------\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\nCreated model: CLASS_MODEL_2 \n\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","# End of Script"],"enabled":true,"result":{"startTime":1714746836988,"interpreter":"md.medium","endTime":1714746837049,"results":[{"message":"<h1 id=\"end-of-script\">End of Script<\/h1>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md"],"enabled":true,"result":{"startTime":1714746837129,"interpreter":"md.medium","endTime":1714746837186,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]